﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace Data
{
    public class Conexao
    {

        private static Conexao instance;

        SqlTransaction transaction = null;
        SqlConnection connection = null;

        public SqlConnection conexao()
        {
            //Data Source=(local);Initial Catalog=FARMACIA;User ID=vitor

            //string conec = "Data Source=(local);Initial Catalog=BANCOTCC;User ID=vitor;Password=123;Language=Portuguese";

            //string conec = "Data Source=(local);Initial Catalog=FARMACIA;Integrated Security=True";


           //string conec = "Data Source=.\\SQLEXPRESS;AttachDbFilename=F:\\TCC\\Banco\\BancoTeste.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            string conec = "Data Source=(local);Initial Catalog=BANCOTCC;Integrated Security=True";

            string conec = "Data Source=REVPC;Initial Catalog=BancoREV;Integrated Security=True";

            //string conec = "Data Source=.\\SQLEXPRESS;AttachDbFilename=D:\\Users\\RODRIGO\\Desktop\\Projeto_TCC\\Banco\\BancoTeste.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";


            //string conec =  "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Users\\Vitor\\Documents\\Visual Studio 2010\\tcc_novo\\BancoTCC.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            SqlConnection cn = new SqlConnection(conec);

            return cn;
        }

        public static Conexao getInstance
        {
            get
            {
                if (instance == null)
                {
                    instance = new Conexao();
                }
                return instance;
            }
        }

        public SqlConnection abrirConexao()
        {
            SqlConnection cn = conexao();
            try
            {
                cn.Open();
                return cn;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void fecharConexao(SqlConnection cn)
        {
            try
            {
                cn.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public DataTable executeQuery(string sql)
        {
            try
            {
                SqlConnection c = abrirConexao();

                SqlCommand sqlComm = new SqlCommand(sql, c);

                sqlComm.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(sqlComm);

                DataTable dt = new DataTable();
                da.Fill(dt);

                fecharConexao(c);

                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public SqlDataAdapter executeQueryRelatorio(string sql)
        {
            try
            {
                SqlConnection c = abrirConexao();

                SqlCommand sqlComm = new SqlCommand(sql, c);

                //sqlComm.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(sqlComm);

                fecharConexao(c);

                return da;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public void CommitTransacao()
        {
            transaction.Commit();
            transaction = null;
            fecharConexao(connection);
        }

        public string executeQueryComStringRetorno(string sql)
        {
            try
            {
                string dado;

                SqlCommand sqlComm = new SqlCommand(sql, abrirConexao());

                sqlComm.ExecuteNonQuery();

                SqlDataAdapter da = new SqlDataAdapter(sqlComm);

                DataTable dt = new DataTable();
                da.Fill(dt);

                dado = dt.Rows[0][0].ToString();

                return dado;
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public bool execute(string sql, bool commit_automatico = true)
        {
            try
            {
                //SqlConnection c = abrirConexao();
                SqlCommand sqlComm;
                if (!commit_automatico)
                {
                    if (connection == null || connection.State != ConnectionState.Open)
                    {
                        connection = abrirConexao();
                    }
                    if (transaction == null)
                    {
                        transaction = connection.BeginTransaction();
                    }

                    sqlComm = new SqlCommand(sql, connection, transaction);
                }
                else
                {
                    connection = abrirConexao();
                    sqlComm = new SqlCommand(sql, connection);
                }

                sqlComm.ExecuteNonQuery();

                if (commit_automatico)
                {
                    fecharConexao(connection);
                }

                /*if (commit_automatico)
                {
                    CommitTransacao();
                }*/

                return true;
            }
            catch (Exception e)
            {
                if (!commit_automatico)
                {                    
                    transaction.Rollback();
                    transaction = null;                    
                }
                MessageBox.Show(e.Message);
                fecharConexao(connection);                              
                return false;
            }
        }
    }
}

